//订单接口
var express = require('express');
var router = express.Router();
const mysql = require('mysql2/promise');
// 导入连接数据库key
const pool = require('../MySQLkey/kry');
// 导入加密库
const bcrypt = require('bcrypt');
// 导入工具类
const tools = require('../tool/tool');

/* GET orders listing. */
//获取订单信息 - 支持分页、筛选、排序
router.get('/', async function(req, res) {
  let connection;
  try {
    connection = await pool.getConnection();

    // 获取查询参数
    const {
      user_id,
      order_status,
      payment_status,
      page = 1,
      limit = 10,
      start_date,
      end_date,
      order_no,
      sort_by = 'created_at',
      sort_order = 'DESC'
    } = req.query;

    // 验证分页参数
    const pagination = tools.validatePagination(page, limit, 50); // 最大50条

    // 构建查询条件
    let whereConditions = [];
    let params = [];

    // 用户ID筛选
    if (user_id) {
      if (!tools.validateUserId(user_id)) {
        return tools.handleError(res, null, '用户ID格式不正确', 400);
      }
      whereConditions.push('user_id = ?');
      params.push(parseInt(user_id));
    }

    // 订单状态筛选
    if (order_status !== undefined) {
      const status = parseInt(order_status);
      if (status < 0 || status > 5) {
        return tools.handleError(res, null, '订单状态值无效', 400);
      }
      whereConditions.push('order_status = ?');
      params.push(status);
    }

    // 支付状态筛选
    if (payment_status !== undefined) {
      const status = parseInt(payment_status);
      if (status < 0 || status > 2) {
        return tools.handleError(res, null, '支付状态值无效', 400);
      }
      whereConditions.push('payment_status = ?');
      params.push(status);
    }

    // 订单号查询
    if (order_no) {
      whereConditions.push('order_no LIKE ?');
      params.push(`%${order_no}%`);
    }

    // 时间范围筛选
    if (start_date) {
      whereConditions.push('created_at >= ?');
      params.push(start_date);
    }
    if (end_date) {
      whereConditions.push('created_at <= ?');
      params.push(end_date);
    }

    // 验证排序字段
    const allowedSortFields = ['created_at', 'updated_at', 'total_amount', 'order_status'];
    const sortField = allowedSortFields.includes(sort_by) ? sort_by : 'created_at';
    const sortDirection = sort_order.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';

    // 构建基础查询语句
    let baseQuery = `
      SELECT
        id, order_no, user_id, total_amount, discount_amount,
        shipping_fee, actual_amount, payment_method, payment_status,
        order_status, shipping_address, remark, paid_at, shipped_at,
        received_at, created_at, updated_at
      FROM orders
    `;

    // 添加WHERE条件
    if (whereConditions.length > 0) {
      baseQuery += ' WHERE ' + whereConditions.join(' AND ');
    }

    // 获取总数
    let countQuery = 'SELECT COUNT(*) as total FROM orders';
    if (whereConditions.length > 0) {
      countQuery += ' WHERE ' + whereConditions.join(' AND ');
    }

    const [countResult] = await connection.query(countQuery, params);
    const total = countResult[0].total;

    // 添加排序和分页
    baseQuery += ` ORDER BY ${sortField} ${sortDirection} LIMIT ? OFFSET ?`;
    params.push(pagination.limit, pagination.offset);

    // 执行查询
    const [rows] = await connection.query(baseQuery, params);

    // 处理shipping_address JSON字段
    const processedRows = rows.map(row => ({
      ...row,
      shipping_address: parseJsonSafely(row.shipping_address)
    }));

    // 返回结果
    tools.handleSuccess(res, {
      orders: processedRows,
      pagination: {
        current_page: pagination.page,
        per_page: pagination.limit,
        total: total,
        total_pages: Math.ceil(total / pagination.limit)
      }
    }, '订单列表获取成功');

  } catch (err) {
    tools.handleError(res, err, '获取订单列表失败');
  } finally {
    if (connection) connection.release();
  }
});

/* GET test orders functionality */
//测试订单功能
router.get('/test', async function(req, res) {
  let connection;
  try {
    // 测试数据库连接
    connection = await pool.getConnection();
    console.log('数据库连接成功');

    // 测试工具类
    const testResult = tools.validateUserId(123);
    console.log('工具类测试:', testResult);

    // 测试orders表查询
    const [rows] = await connection.query('SELECT COUNT(*) as count FROM orders');
    console.log('orders表查询成功:', rows[0].count);

    res.json({
      success: true,
      message: '订单功能测试成功',
      data: {
        database_connection: '正常',
        tools_available: testResult,
        orders_table_count: rows[0].count
      }
    });

  } catch (err) {
    console.error('测试失败:', err);
    res.status(500).json({
      success: false,
      message: '测试失败',
      error: err.message,
      stack: err.stack
    });
  } finally {
    if (connection) connection.release();
  }
});

/* GET single order by ID */
//根据订单ID获取订单详情
router.get('/:id', async function(req, res) {
  let connection;
  try {
    connection = await pool.getConnection();

    const orderId = req.params.id;

    // 验证订单ID
    if (!tools.validateUserId(orderId)) {
      return tools.handleError(res, null, '订单ID格式不正确', 400);
    }

    // 查询订单基本信息
    const orderQuery = `
      SELECT
        id, order_no, user_id, total_amount, discount_amount,
        shipping_fee, actual_amount, payment_method, payment_status,
        order_status, shipping_address, remark, paid_at, shipped_at,
        received_at, created_at, updated_at
      FROM orders
      WHERE id = ?
    `;

    const [orderRows] = await connection.query(orderQuery, [orderId]);

    if (orderRows.length === 0) {
      return tools.handleError(res, null, '订单不存在', 404);
    }

    const order = orderRows[0];

    // 查询订单商品详情
    const itemsQuery = `
      SELECT
        oi.id, oi.product_id, oi.product_name, oi.product_image,
        oi.product_specs, oi.price, oi.quantity, oi.total_amount,
        p.main_image, p.status as product_status
      FROM order_items oi
      LEFT JOIN products p ON oi.product_id = p.id
      WHERE oi.order_id = ?
      ORDER BY oi.id
    `;

    const [itemsRows] = await connection.query(itemsQuery, [orderId]);

    // 处理JSON字段
    const processedOrder = {
      ...order,
      shipping_address: parseJsonSafely(order.shipping_address),
      items: itemsRows.map(item => ({
        ...item,
        product_specs: parseJsonSafely(item.product_specs)
      }))
    };

    tools.handleSuccess(res, processedOrder, '订单详情获取成功');

  } catch (err) {
    tools.handleError(res, err, '获取订单详情失败');
  } finally {
    if (connection) connection.release();
  }
});

/* GET orders by user ID */
//根据用户ID获取用户的订单列表
router.get('/user/:user_id', async function(req, res) {
  let connection;
  try {
    connection = await pool.getConnection();

    const userId = req.params.user_id;
    const {
      order_status,
      payment_status,
      page = 1,
      limit = 10,
      sort_by = 'created_at',
      sort_order = 'DESC'
    } = req.query;

    // 验证用户ID
    if (!tools.validateUserId(userId)) {
      return tools.handleError(res, null, '用户ID格式不正确', 400);
    }

    // 验证分页参数
    const pagination = tools.validatePagination(page, limit, 20);

    // 构建查询条件
    let whereConditions = ['user_id = ?'];
    let params = [parseInt(userId)];

    // 订单状态筛选
    if (order_status !== undefined) {
      const status = parseInt(order_status);
      if (status < 0 || status > 5) {
        return tools.handleError(res, null, '订单状态值无效', 400);
      }
      whereConditions.push('order_status = ?');
      params.push(status);
    }

    // 支付状态筛选
    if (payment_status !== undefined) {
      const status = parseInt(payment_status);
      if (status < 0 || status > 2) {
        return tools.handleError(res, null, '支付状态值无效', 400);
      }
      whereConditions.push('payment_status = ?');
      params.push(status);
    }

    // 验证排序字段
    const allowedSortFields = ['created_at', 'updated_at', 'total_amount', 'order_status'];
    const sortField = allowedSortFields.includes(sort_by) ? sort_by : 'created_at';
    const sortDirection = sort_order.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';

    // 构建查询语句
    let baseQuery = `
      SELECT
        id, order_no, total_amount, discount_amount, shipping_fee,
        actual_amount, payment_method, payment_status, order_status,
        paid_at, shipped_at, received_at, created_at, updated_at
      FROM orders
      WHERE ${whereConditions.join(' AND ')}
    `;

    // 获取总数
    let countQuery = `SELECT COUNT(*) as total FROM orders WHERE ${whereConditions.join(' AND ')}`;
    const [countResult] = await connection.query(countQuery, params);
    const total = countResult[0].total;

    // 添加排序和分页
    baseQuery += ` ORDER BY ${sortField} ${sortDirection} LIMIT ? OFFSET ?`;
    params.push(pagination.limit, pagination.offset);

    // 执行查询
    const [rows] = await connection.query(baseQuery, params);

    // 返回结果
    tools.handleSuccess(res, {
      orders: rows,
      pagination: {
        current_page: pagination.page,
        per_page: pagination.limit,
        total: total,
        total_pages: Math.ceil(total / pagination.limit)
      }
    }, '用户订单列表获取成功');

  } catch (err) {
    tools.handleError(res, err, '获取用户订单列表失败');
  } finally {
    if (connection) connection.release();
  }
});
/* POST create new order */
//创建订单
router.post('/', async function(req, res) {
  let connection;
  try {
    connection = await pool.getConnection();

    // 获取请求数据
    const {
      user_id,
      total_amount,
      discount_amount = 0.00,
      shipping_fee = 0.00,
      actual_amount,
      payment_method,
      payment_status = 0,
      order_status = 0,
      shipping_address,
      remark
    } = req.body;

    // 验证必需字段
    if (!user_id || !total_amount || !actual_amount) {
      return tools.handleError(res, null, '用户ID、订单总金额和实付金额为必填项', 400);
    }

    // 验证用户ID
    if (!tools.validateUserId(user_id)) {
      return tools.handleError(res, null, '用户ID格式不正确', 400);
    }

    // 验证金额格式
    const totalAmount = parseFloat(total_amount);
    const discountAmount = parseFloat(discount_amount);
    const shippingFee = parseFloat(shipping_fee);
    const actualAmount = parseFloat(actual_amount);

    if (totalAmount <= 0 || actualAmount <= 0) {
      return tools.handleError(res, null, '订单金额必须大于0', 400);
    }

    if (discountAmount < 0 || shippingFee < 0) {
      return tools.handleError(res, null, '优惠金额和运费不能为负数', 400);
    }

    // 验证实付金额计算是否正确
    const calculatedAmount = totalAmount - discountAmount + shippingFee;
    if (Math.abs(calculatedAmount - actualAmount) > 0.01) {
      return tools.handleError(res, null, '实付金额计算错误', 400);
    }

    // 验证支付状态
    const paymentStatusValue = parseInt(payment_status);
    if (![0, 1, 2].includes(paymentStatusValue)) {
      return tools.handleError(res, null, '支付状态值无效，只能是0(待支付)、1(已支付)、2(已退款)', 400);
    }

    // 验证订单状态
    const orderStatusValue = parseInt(order_status);
    if (![0, 1, 2, 3, 4, 5].includes(orderStatusValue)) {
      return tools.handleError(res, null, '订单状态值无效，只能是0-5之间的数字', 400);
    }

    // 业务逻辑验证：如果订单状态是待发货或以上，支付状态必须是已支付
    if (orderStatusValue >= 1 && paymentStatusValue !== 1) {
      return tools.handleError(res, null, '订单状态为待发货及以上时，支付状态必须为已支付', 400);
    }

    // 验证用户是否存在
    const [userCheck] = await connection.query(
      'SELECT id FROM users WHERE id = ?',
      [user_id]
    );

    if (userCheck.length === 0) {
      return tools.handleError(res, null, '用户不存在', 400);
    }

    // 生成订单号
    const orderNo = generateOrderNo();

    // 处理收货地址JSON
    let shippingAddressJson = null;
    if (shipping_address) {
      try {
        shippingAddressJson = typeof shipping_address === 'string'
          ? shipping_address
          : JSON.stringify(shipping_address);
      } catch (e) {
        return tools.handleError(res, null, '收货地址格式错误', 400);
      }
    }

    // 插入订单数据
    const insertQuery = `
      INSERT INTO orders (
        order_no, user_id, total_amount, discount_amount,
        shipping_fee, actual_amount, payment_method,
        payment_status, order_status, shipping_address, remark,
        paid_at, shipped_at, received_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;

    // 根据状态设置相应的时间字段
    let paidAt = null;
    let shippedAt = null;
    let receivedAt = null;

    if (paymentStatusValue === 1) {
      paidAt = new Date(); // 已支付：设置支付时间
    }

    if (orderStatusValue === 2) {
      shippedAt = new Date(); // 已发货：设置发货时间
    } else if (orderStatusValue === 3) {
      receivedAt = new Date(); // 已收货：设置收货时间
    }

    const [result] = await connection.query(insertQuery, [
      orderNo,
      user_id,
      totalAmount,
      discountAmount,
      shippingFee,
      actualAmount,
      payment_method || null,
      paymentStatusValue,
      orderStatusValue,
      shippingAddressJson,
      remark || null,
      paidAt,
      shippedAt,
      receivedAt
    ]);

    // 返回创建的订单信息
    tools.handleSuccess(res, {
      id: result.insertId,
      order_no: orderNo,
      payment_status: paymentStatusValue,
      order_status: orderStatusValue,
      paid_at: paidAt,
      shipped_at: shippedAt,
      received_at: receivedAt
    }, '订单创建成功', 200);

  } catch (err) {
    tools.handleError(res, err, '创建订单失败');
  } finally {
    if (connection) connection.release();
  }
});

/* PUT update payment status */
//更新订单支付状态
router.put('/:id/payment-status', async function(req, res) {
  let connection;
  try {
    connection = await pool.getConnection();

    const orderId = req.params.id;
    const { payment_status } = req.body;

    // 验证订单ID
    if (!tools.validateUserId(orderId)) {
      return tools.handleError(res, null, '订单ID格式不正确', 400);
    }

    // 验证支付状态
    if (payment_status === undefined || payment_status === null) {
      return tools.handleError(res, null, '支付状态为必填项', 400);
    }

    const paymentStatus = parseInt(payment_status);
    if (![0, 1, 2].includes(paymentStatus)) {
      return tools.handleError(res, null, '支付状态值无效，只能是0(待支付)、1(已支付)、2(已退款)', 400);
    }

    // 查询订单是否存在
    const [orderCheck] = await connection.query(
      'SELECT id, payment_status, order_status FROM orders WHERE id = ?',
      [orderId]
    );

    if (orderCheck.length === 0) {
      return tools.handleError(res, null, '订单不存在', 404);
    }

    const currentOrder = orderCheck[0];

    // 业务逻辑验证
    if (currentOrder.payment_status === paymentStatus) {
      return tools.handleError(res, null, '支付状态未发生变化', 400);
    }

    // 构建更新语句
    let updateQuery = 'UPDATE orders SET payment_status = ?, updated_at = CURRENT_TIMESTAMP';
    let params = [paymentStatus];

    // 根据支付状态更新相关时间字段
    if (paymentStatus === 1) {
      // 已支付：设置支付时间，如果订单状态是待支付则改为待发货
      updateQuery += ', paid_at = CURRENT_TIMESTAMP';
      if (currentOrder.order_status === 0) {
        updateQuery += ', order_status = 1';
      }
    } else if (paymentStatus === 2) {
      // 已退款：如果订单状态不是已完成或已取消，则设为已取消
      if (![4, 5].includes(currentOrder.order_status)) {
        updateQuery += ', order_status = 5';
      }
    }

    updateQuery += ' WHERE id = ?';
    params.push(orderId);

    // 执行更新
    await connection.query(updateQuery, params);

    // 获取更新后的订单信息
    const [updatedOrder] = await connection.query(
      'SELECT * FROM orders WHERE id = ?',
      [orderId]
    );

    // 处理JSON字段
    const orderData = {
      ...updatedOrder[0],
      shipping_address: parseJsonSafely(updatedOrder[0].shipping_address)
    };

    tools.handleSuccess(res, orderData, '支付状态更新成功');

  } catch (err) {
    tools.handleError(res, err, '更新支付状态失败');
  } finally {
    if (connection) connection.release();
  }
});

/* PUT update order status */
//更新订单状态
router.put('/:id/order-status', async function(req, res) {
  let connection;
  try {
    connection = await pool.getConnection();

    const orderId = req.params.id;
    const { order_status } = req.body;

    // 验证订单ID
    if (!tools.validateUserId(orderId)) {
      return tools.handleError(res, null, '订单ID格式不正确', 400);
    }

    // 验证订单状态
    if (order_status === undefined || order_status === null) {
      return tools.handleError(res, null, '订单状态为必填项', 400);
    }

    const orderStatus = parseInt(order_status);
    if (![0, 1, 2, 3, 4, 5].includes(orderStatus)) {
      return tools.handleError(res, null, '订单状态值无效，只能是0-5之间的数字', 400);
    }

    // 查询订单是否存在
    const [orderCheck] = await connection.query(
      'SELECT id, payment_status, order_status FROM orders WHERE id = ?',
      [orderId]
    );

    if (orderCheck.length === 0) {
      return tools.handleError(res, null, '订单不存在', 404);
    }

    const currentOrder = orderCheck[0];

    // 业务逻辑验证
    if (currentOrder.order_status === orderStatus) {
      return tools.handleError(res, null, '订单状态未发生变化', 400);
    }

    // 状态流转验证
    const statusTransitions = {
      0: [1, 5], // 待支付 -> 待发货、已取消
      1: [2, 5], // 待发货 -> 已发货、已取消
      2: [3,4,5], // 已发货 -> 已收货、已完成、已取消
      3: [4],    // 已收货 -> 已完成
      4: [],     // 已完成 -> 无法变更
      5: []      // 已取消 -> 无法变更
    };

    if (!statusTransitions[currentOrder.order_status].includes(orderStatus)) {
      return tools.handleError(res, null, '订单状态流转不合法', 400);
    }

    // 特殊验证：待发货状态需要已支付
    if (orderStatus === 1 && currentOrder.payment_status !== 1) {
      return tools.handleError(res, null, '订单未支付，无法设为待发货状态', 400);
    }

    // 构建更新语句
    let updateQuery = 'UPDATE orders SET order_status = ?, updated_at = CURRENT_TIMESTAMP';
    let params = [orderStatus];

    // 根据订单状态更新相关时间字段
    if (orderStatus === 2) {
      // 已发货：设置发货时间
      updateQuery += ', shipped_at = CURRENT_TIMESTAMP';
    } else if (orderStatus === 3) {
      // 已收货：设置收货时间
      updateQuery += ', received_at = CURRENT_TIMESTAMP';
    }

    updateQuery += ' WHERE id = ?';
    params.push(orderId);

    // 执行更新
    await connection.query(updateQuery, params);

    // 获取更新后的订单信息
    const [updatedOrder] = await connection.query(
      'SELECT * FROM orders WHERE id = ?',
      [orderId]
    );

    // 处理JSON字段
    const orderData = {
      ...updatedOrder[0],
      shipping_address: parseJsonSafely(updatedOrder[0].shipping_address)
    };

    tools.handleSuccess(res, orderData, '订单状态更新成功');

  } catch (err) {
    console.error('更新订单状态详细错误:', err);
    console.error('订单ID:', req.params.id);
    console.error('请求状态:', req.body.order_status);
    tools.handleError(res, err, '更新订单状态失败');
  } finally {
    if (connection) connection.release();
  }
});

// 安全解析JSON的辅助函数
function parseJsonSafely(jsonData) {
  if (!jsonData) return null;

  // 如果已经是对象，直接返回
  if (typeof jsonData === 'object') {
    return jsonData;
  }

  // 如果是字符串，尝试解析
  if (typeof jsonData === 'string') {
    try {
      return JSON.parse(jsonData);
    } catch (e) {
      console.warn('JSON解析失败:', jsonData);
      return jsonData; // 解析失败时返回原字符串
    }
  }

  return jsonData;
}

// 生成订单号的辅助函数
//订单号格式：ORD + 年月日时分秒 + 3位随机数
function generateOrderNo() {
  const now = new Date();
  const year = now.getFullYear();
  const month = String(now.getMonth() + 1).padStart(2, '0');
  const day = String(now.getDate()).padStart(2, '0');
  const hours = String(now.getHours()).padStart(2, '0');
  const minutes = String(now.getMinutes()).padStart(2, '0');
  const seconds = String(now.getSeconds()).padStart(2, '0');
  const random = Math.floor(Math.random() * 1000).toString().padStart(3, '0');

  return `ORD${year}${month}${day}${hours}${minutes}${seconds}${random}`;
}

/* GET debug order status */
//调试订单状态
router.get('/debug/:id', async function(req, res) {
  let connection;
  try {
    connection = await pool.getConnection();

    const orderId = req.params.id;

    // 查询订单详细信息
    const [orderRows] = await connection.query(
      'SELECT id, order_no, payment_status, order_status, created_at FROM orders WHERE id = ?',
      [orderId]
    );

    if (orderRows.length === 0) {
      return res.json({
        success: false,
        message: '订单不存在',
        order_id: orderId
      });
    }

    const order = orderRows[0];

    // 状态流转规则
    const statusTransitions = {
      0: [1, 5], // 待支付 -> 待发货、已取消
      1: [2, 5], // 待发货 -> 已发货、已取消
      2: [3, 5], // 已发货 -> 已收货、已取消
      3: [4],    // 已收货 -> 已完成
      4: [],     // 已完成 -> 无法变更
      5: []      // 已取消 -> 无法变更
    };

    const statusNames = {
      0: '待支付',
      1: '待发货',
      2: '已发货',
      3: '已收货',
      4: '已完成',
      5: '已取消'
    };

    const paymentStatusNames = {
      0: '待支付',
      1: '已支付',
      2: '已退款'
    };

    res.json({
      success: true,
      message: '订单状态调试信息',
      data: {
        order_info: {
          id: order.id,
          order_no: order.order_no,
          current_payment_status: order.payment_status,
          current_order_status: order.order_status,
          payment_status_name: paymentStatusNames[order.payment_status],
          order_status_name: statusNames[order.order_status],
          created_at: order.created_at
        },
        allowed_transitions: statusTransitions[order.order_status],
        allowed_transitions_names: statusTransitions[order.order_status].map(status => ({
          status: status,
          name: statusNames[status]
        })),
        can_update_to_4: statusTransitions[order.order_status].includes(4),
        validation_rules: {
          need_payment_for_status_1: '待发货状态需要已支付',
          status_flow: '0->1,5 | 1->2,5 | 2->3,5 | 3->4 | 4->无 | 5->无'
        }
      }
    });

  } catch (err) {
    console.error('调试订单状态失败:', err);
    res.status(500).json({
      success: false,
      message: '调试失败',
      error: err.message
    });
  } finally {
    if (connection) connection.release();
  }
});

module.exports = router;
